package com.nicolas.sqlitedemo;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class RecordUtils extends SQLiteOpenHelper {

	private static final String LOG = RecordUtils.class.getName();
	private static final String DATA_BASE_NAME = "nicolas.db";
	private static final int DATA_BASE_VERSION = 1;
	private static RecordUtils instance;
	private static List<String> tables;
	private static final String TABLE_NICOLAS_RECORD = "nicolas";
	private static final String NICOLAS_COLUMN_ID = "id";
	private static final String NICOLAS_COLUMN_NAME = "name";

	private static final String CREATE_TABLE_SQL_NICOLAS = " CREATE TABLE IF NOT EXISTS %1$S ("
			+ NICOLAS_COLUMN_ID
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ NICOLAS_COLUMN_NAME + " TEXT) ";

	private static final String DROP_TABLE_SQL = "DROP TABLE IF EXISTS %1$s";

	private RecordUtils(Context context) {
		super(context, DATA_BASE_NAME, null, DATA_BASE_VERSION);
	}

	public static RecordUtils getInstance(Context context) {
		if (instance == null) {
			instance = new RecordUtils(context);
		}
		if (tables == null) {
			tables = new ArrayList<String>();
			tables.add(TABLE_NICOLAS_RECORD);
		}
		return instance;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		if (db == null || tables == null) {
			return;
		}
		db.beginTransaction();
		try {
			db.execSQL(String.format(CREATE_TABLE_SQL_NICOLAS,
					TABLE_NICOLAS_RECORD));

			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.v(LOG, "database tables create error!");
		} finally {
			db.endTransaction();
		}
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		if (db == null) {
			return;
		}
		try {
			dropRecord(db);
			onCreate(db);
		} catch (Exception e) {
			Log.e(LOG, "");
		} finally {
			// close;
		}
	}

	private void dropRecord(SQLiteDatabase db) {
		db.beginTransaction();
		try {
			for (String table : tables) {
				if (table != null && table.length() > 0) {
					db.execSQL(String.format(DROP_TABLE_SQL, table));
				}
			}
			db.setTransactionSuccessful();
		} catch (Exception e) {
			Log.v(LOG, "drop table error!");
		} finally {
			db.endTransaction();
		}
	}

	public boolean writeTextStorage(String name) {
		boolean b = false;
		SQLiteDatabase db = null;
		try {
			db = instance.getWritableDatabase();

			ContentValues value = new ContentValues();
			value.put(NICOLAS_COLUMN_NAME, name);
			db.insert(TABLE_NICOLAS_RECORD, null, value);

			b = true;
		} catch (Exception e) {
			Log.v(LOG, e.getMessage());
		} finally {
			// empty here.
		}
		return b;
	}

	public String getName() {
		SQLiteDatabase db = null;
		Cursor cursor = null;
		String name = null;
		try {
			db = instance.getReadableDatabase();
			cursor = db.query(TABLE_NICOLAS_RECORD, new String[] {
					NICOLAS_COLUMN_ID, NICOLAS_COLUMN_NAME }, null, null, null,
					null, null);
			if (cursor.moveToFirst()) {
				// long id = cursor.getLong(cursor
				// .getColumnIndex(NICOLAS_COLUMN_ID));
				name = cursor.getString(cursor
						.getColumnIndex(NICOLAS_COLUMN_NAME));
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}

		return name;
	}

	public List<String> getAllNames() {
		SQLiteDatabase db = null;
		Cursor cursor = null;
		List<String> names = new ArrayList<String>();
		try {
			db = instance.getReadableDatabase();
			cursor = db.query(TABLE_NICOLAS_RECORD, new String[] {
					NICOLAS_COLUMN_ID, NICOLAS_COLUMN_NAME }, null, null, null,
					null, null);
			if (cursor.moveToFirst()) {
				// long id = cursor.getLong(cursor
				// .getColumnIndex(NICOLAS_COLUMN_ID));
				do {
					String name = cursor.getString(cursor
							.getColumnIndex(NICOLAS_COLUMN_NAME));
					names.add(name);
				} while (cursor.moveToFirst());
			}
		} catch (Exception e) {
		} finally {
			if (cursor != null) {
				cursor.close();
			}
		}

		return names;
	}
}
